package cn.edu.hbvtc.qms.web.utils;

import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import cn.edu.hbvtc.qms.domain.Evaluation;
import cn.edu.hbvtc.qms.domain.Term;

import jxl.Workbook;
import jxl.format.*;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelDocWriter {
	
	public static void buildExcelDoc(List<Evaluation> evaluations, OutputStream out) {
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(out);
			WritableSheet ws = wwb.createSheet("教学评价结果列表", 0);
			
            /***********设置列宽*****************/
            ws.setColumnView(0, 15); // 第1列
            ws.setColumnView(1, 15); // 第2列
            ws.setColumnView(2, 20);// 第3列
            ws.setColumnView(3, 15); // 第4列
            ws.setColumnView(4, 15); // 第5列
            ws.setColumnView(5, 15); // 第6列
            ws.setColumnView(6, 15); // 第7列
            
            ws.setColumnView(7, 10); // 第8列——总分值
            ws.setColumnView(8, 10); // 第9列 ——总A数
            ws.setColumnView(9, 10); // 第10列 ——总B数
            ws.setColumnView(10, 10); // 第11列 ——总C数
            ws.setColumnView(11, 10); // 第12列 ——总D数
            
            ws.setColumnView(12, 20); // 第13列 ——各项评分指标得分（共20项）
            ws.setColumnView(13, 20);
            ws.setColumnView(14, 20);
            ws.setColumnView(15, 20);
            ws.setColumnView(16, 20);
            ws.setColumnView(17, 20);
            ws.setColumnView(18, 20);
            ws.setColumnView(19, 20);
            ws.setColumnView(20, 20);
            ws.setColumnView(21, 20);
            ws.setColumnView(22, 20); 
            ws.setColumnView(23, 20);
            ws.setColumnView(24, 20);
            ws.setColumnView(25, 20);
            ws.setColumnView(26, 20);
            ws.setColumnView(27, 20);
            ws.setColumnView(28, 20);
            ws.setColumnView(29, 20);
            ws.setColumnView(30, 20);
            ws.setColumnView(31, 20);
                        
            /*****************设置单元格字体***************/
            //字体
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD);
            WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
            WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            
            /*****************以下设置几种格式的单元格*************/
            // 用于标题
            WritableCellFormat wcf_title = new WritableCellFormat(BoldFont);
            wcf_title.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐
            wcf_title.setWrap(true); // 是否换行
            
            // 用于表格标题
            WritableCellFormat wcf_tabletitle = new WritableCellFormat(tableFont);
            wcf_tabletitle.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_tabletitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_tabletitle.setAlignment(Alignment.CENTRE); // 水平对齐
            wcf_tabletitle.setWrap(true); // 是否换行
            
            // 用于正文左
            WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
            wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_left.setAlignment(Alignment.LEFT);
            wcf_left.setWrap(true); // 是否换行

            // 用于正文左
            WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
            wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_center.setAlignment(Alignment.CENTRE);
            wcf_center.setWrap(true); // 是否换行

            // 用于正文右
            WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);
            wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_right.setAlignment(Alignment.RIGHT);
            wcf_right.setWrap(false); // 是否换行

            // 用于跨行
            WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont);
            wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐
            wcf_merge.setAlignment(Alignment.LEFT);
            wcf_merge.setWrap(true); // 是否换行

            WritableCellFormat wcf_table = new WritableCellFormat(NormalFont);
            wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_table.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_table.setAlignment(Alignment.CENTRE);
            wcf_table.setBackground(Colour.GRAY_25);
            wcf_table.setWrap(true); // 是否换行
            
            /** *//** ************单元格格式设置完成****************** */
            
            //合并单元格,注意mergeCells(col0,row0,col1,row1) --列从0开始,col1为你要合并到第几列,行也一样
            ws.mergeCells(0, 0, 31, 0);
            Label label00 = new Label(0, 0, "湖北职业技术学院教学评价结果汇总表", wcf_title);
            ws.addCell(label00);
            
            if (evaluations != null) {
	            Iterator<Evaluation> iter = evaluations.iterator();
	            Evaluation evaluation = iter.next();

	            Term term = evaluation.getTerm();//获得教学评价所在学期
	            String termStr = term.getStartYear() + "—" + term.getEndYear() + "学年度";
	            if (term.getTermOrder() == 1) {
	            	termStr += " 上学期";
	            } else {
	            	termStr += " 下学期";
	            }
	            termStr += "    ";
	            
	            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	            String date = sdf.format(new Date());
	            
	            ws.mergeCells(0, 1, 7, 1);
	            Label label01 = new Label(0, 1, "学期：" + termStr + "打印日期：" + date);
	            ws.addCell(label01);
				
				//表格标题
				ws.addCell(new Label(0, 2, "院系", wcf_tabletitle));
				ws.addCell(new Label(1, 2, "班级", wcf_tabletitle));
				ws.addCell(new Label(2, 2, "课程名称", wcf_tabletitle));
				ws.addCell(new Label(3, 2, "教师", wcf_tabletitle));
				ws.addCell(new Label(4, 2, "参评人数", wcf_tabletitle));
				ws.addCell(new Label(5, 2, "班级人数", wcf_tabletitle));
				ws.addCell(new Label(6, 2, "参评比率", wcf_tabletitle));
				ws.addCell(new Label(7, 2, "总分值", wcf_tabletitle));
				
				ws.addCell(new Label(8, 2, "总A数", wcf_tabletitle));
				ws.addCell(new Label(9, 2, "总B数", wcf_tabletitle));
				ws.addCell(new Label(10, 2, "总C数", wcf_tabletitle));
				ws.addCell(new Label(11, 2, "总D数", wcf_tabletitle));
	            
				ws.addCell(new Label(12, 2, "目标明确，突出重点", wcf_tabletitle));
				ws.addCell(new Label(13, 2, "符合专业要求，突出职业技术教育特点，注重学生素质与能力培养", wcf_tabletitle));
				ws.addCell(new Label(14, 2, "教学内容符合课程计划和教学大纲", wcf_tabletitle));
				ws.addCell(new Label(15, 2, "教学项目设计或案例选择正确，与职业实际联系紧密", wcf_tabletitle));
				ws.addCell(new Label(16, 2, "新理论、新知识、新方法、新进展的传授", wcf_tabletitle));
				ws.addCell(new Label(17, 2, "工作情景或教学情景设计合理，资源利用充分", wcf_tabletitle));
				ws.addCell(new Label(18, 2, "内容与方式与学生认知能力符合，因材施教", wcf_tabletitle));
				ws.addCell(new Label(19, 2, "能根据教学内容选择教学模式，方法灵活", wcf_tabletitle));
				ws.addCell(new Label(20, 2, "课堂学生互动、师生互动良好", wcf_tabletitle));
				ws.addCell(new Label(21, 2, "注重启发示范，充分调动学生的学习主动性与积极性，课堂气氛活跃", wcf_tabletitle));
				
				ws.addCell(new Label(22, 2, "训练基本技能，发掘个人潜力，培养学习能力", wcf_tabletitle));
				ws.addCell(new Label(23, 2, "能充分利用教具、器材、设备创设教学情景，进行生动、直观的教学", wcf_tabletitle));
				ws.addCell(new Label(24, 2, "教学态度认真负责，耐心细致", wcf_tabletitle));
				ws.addCell(new Label(25, 2, "教学准备充分，教案书写规范", wcf_tabletitle));
				ws.addCell(new Label(26, 2, "板书工整合理，普通话教学，音量、语速适中", wcf_tabletitle));
				ws.addCell(new Label(27, 2, "公平对待每一个学生，注意每一个学生学习进步", wcf_tabletitle));
				ws.addCell(new Label(28, 2, "组织教学严密，步骤安排合理，时间安排恰当", wcf_tabletitle));
				ws.addCell(new Label(29, 2, "能管教管导，注重学生全面素质培养，课堂纪律好", wcf_tabletitle));
				ws.addCell(new Label(30, 2, "学生能掌握教学内容", wcf_tabletitle));
				ws.addCell(new Label(31, 2, "课堂回答问题正确率高", wcf_tabletitle));
				
				//教学评价结果列表
				ws.addCell(new Label(0, 3, evaluation.getClazz().getMajor().getDept().getDeptName()));
				ws.addCell(new Label(1, 3, evaluation.getClazz().getClazzId()));
				ws.addCell(new Label(2, 3, evaluation.getCourse().getCourseName()));
				ws.addCell(new Label(3, 3, evaluation.getEvaluated().getNickname()));
				ws.addCell(new Label(4, 3, evaluation.getEvalNumber()));
				ws.addCell(new Label(5, 3, String.valueOf(evaluation.getClazz().getStuNumber())));
				ws.addCell(new Label(6, 3, String.valueOf(new DecimalFormat("##.00").format(Double.parseDouble(evaluation.getEvalNumber()) / evaluation.getClazz().getStuNumber() * 100))+ "%"));
				ws.addCell(new Label(7, 3, evaluation.getTotalScore()));
				
				ws.addCell(new Label(8, 3, evaluation.getTotalA() + "%"));
				ws.addCell(new Label(9, 3, evaluation.getTotalB() + "%"));
				ws.addCell(new Label(10, 3, evaluation.getTotalC() + "%"));
				ws.addCell(new Label(11, 3, evaluation.getTotalD() + "%"));
	            
				ws.addCell(new Label(12, 3, evaluation.getScore1()));
				ws.addCell(new Label(13, 3, evaluation.getScore2()));
				ws.addCell(new Label(14, 3, evaluation.getScore3()));
				ws.addCell(new Label(15, 3, evaluation.getScore4()));
				ws.addCell(new Label(16, 3, evaluation.getScore5()));
				ws.addCell(new Label(17, 3, evaluation.getScore6()));
				ws.addCell(new Label(18, 3, evaluation.getScore7()));
				ws.addCell(new Label(19, 3, evaluation.getScore8()));
				ws.addCell(new Label(20, 3, evaluation.getScore9()));
				ws.addCell(new Label(21, 3, evaluation.getScore10()));
				
				ws.addCell(new Label(22, 3, evaluation.getScore11()));
				ws.addCell(new Label(23, 3, evaluation.getScore12()));
				ws.addCell(new Label(24, 3, evaluation.getScore13()));
				ws.addCell(new Label(25, 3, evaluation.getScore14()));
				ws.addCell(new Label(26, 3, evaluation.getScore15()));
				ws.addCell(new Label(27, 3, evaluation.getScore16()));
				ws.addCell(new Label(28, 3, evaluation.getScore17()));
				ws.addCell(new Label(29, 3, evaluation.getScore18()));
				ws.addCell(new Label(30, 3, evaluation.getScore19()));
				ws.addCell(new Label(31, 3, evaluation.getScore20()));
				
				int line = 4;
				while (iter.hasNext()) {
	            	evaluation = iter.next();

					ws.addCell(new Label(0, line, evaluation.getClazz().getMajor().getDept().getDeptName()));
					ws.addCell(new Label(1, line, evaluation.getClazz().getClazzId()));
					ws.addCell(new Label(2, line, evaluation.getCourse().getCourseName()));
					ws.addCell(new Label(3, line, evaluation.getEvaluated().getNickname()));
					ws.addCell(new Label(4, line, evaluation.getEvalNumber()));
					ws.addCell(new Label(5, line, String.valueOf(evaluation.getClazz().getStuNumber())));
					ws.addCell(new Label(6, line, String.valueOf(new DecimalFormat("##.00").format(Double.parseDouble(evaluation.getEvalNumber()) / evaluation.getClazz().getStuNumber() * 100)) + "%"));
					ws.addCell(new Label(7, line, evaluation.getTotalScore()));
					
					ws.addCell(new Label(8, line, evaluation.getTotalA() + "%"));
					ws.addCell(new Label(9, line, evaluation.getTotalB() + "%"));
					ws.addCell(new Label(10, line, evaluation.getTotalC() + "%"));
					ws.addCell(new Label(11, line, evaluation.getTotalD() + "%"));
		            
					ws.addCell(new Label(12, line, evaluation.getScore1()));
					ws.addCell(new Label(13, line, evaluation.getScore2()));
					ws.addCell(new Label(14, line, evaluation.getScore3()));
					ws.addCell(new Label(15, line, evaluation.getScore4()));
					ws.addCell(new Label(16, line, evaluation.getScore5()));
					ws.addCell(new Label(17, line, evaluation.getScore6()));
					ws.addCell(new Label(18, line, evaluation.getScore7()));
					ws.addCell(new Label(19, line, evaluation.getScore8()));
					ws.addCell(new Label(20, line, evaluation.getScore9()));
					ws.addCell(new Label(21, line, evaluation.getScore10()));
					
					ws.addCell(new Label(22, line, evaluation.getScore11()));
					ws.addCell(new Label(23, line, evaluation.getScore12()));
					ws.addCell(new Label(24, line, evaluation.getScore13()));
					ws.addCell(new Label(25, line, evaluation.getScore14()));
					ws.addCell(new Label(26, line, evaluation.getScore15()));
					ws.addCell(new Label(27, line, evaluation.getScore16()));
					ws.addCell(new Label(28, line, evaluation.getScore17()));
					ws.addCell(new Label(29, line, evaluation.getScore18()));
					ws.addCell(new Label(30, line, evaluation.getScore19()));
					ws.addCell(new Label(31, line, evaluation.getScore20()));
					
	    			line++;
	            }
            }
			wwb.write();
			wwb.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
}
